import { connection } from '../app/database/mysql';
import { cornerStatusChangeByType, saveCorner } from '../corner/common.service';
import { tutorModel } from './tutor.model';

/**
 * 获取内容
 */
export const getTutor = async (pageSize: number, pageNo: number) => {
  // 准备查询
  let statement = `
    SELECT *, COUNT(id) OVER () AS total FROM boroughs
  `;
  let where = []
  // if (status >= 0) {
  //   where.push(`status = ` + status)
  // }
  if (where.length > 0) {
    statement += ` where `
    for (let i = 0; i < where.length; i++) {
      statement += where[i]
      if (i < where.length - 1) {
        statement += ` and `
      }
    }
  }
  statement += ` ORDER by id DESC limit ` + (pageNo > 0 ? (pageNo - 1) * pageSize : 0) + `,` + pageSize;

  // 执行查询
  const [data] = await connection.promise().query(statement);
  let totalCount: any;
  if (data[0] && data[0].total) totalCount = data[0].total;
  let totalPage = (totalCount % pageSize) > 0 ? parseInt(`${totalCount / pageSize}`) + 1 : totalCount / pageSize;

  let res = {
    data,
    totalPage,
    totalCount
  }
  // 提供数据
  return res;
}

/**
 * 按 ID 获取内容
 */
export const getIdTutor = async (postId: number) => {
  // 准备查询
  const statement = `
    SELECT * FROM boroughs
    WHERE boroughs_code = ?
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, postId);

  // 提供数据
  return data[0];
}

/**
 * 按 两位数 获取
 */
export const getTwoBoroughs = async (code: number) => {
  // 准备查询
  const statement = `
    SELECT * FROM boroughs
    WHERE two_num_code = ?
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, code);

  // 提供数据
  return data;
}

/**
 * 按 三位数 获取
 */
export const getThreeBoroughs = async (code: number) => {
  // 准备查询
  const statement = `
    SELECT * FROM boroughs
    WHERE boroughs_code = ?
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, code);

  // 提供数据
  return data;
}

/**
 * 按 四位数 获取
 */
export const getFourBoroughs = async (code: number) => {
  // 准备查询
  const statement = `
    SELECT * FROM boroughs
    WHERE four_num_code = ?
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, code);

  // 提供数据
  return data;
}

/**
 * 创建内容
 */
export const createTutor = async (post: tutorModel) => {
  // 准备查询
  const statement = `
    INSERT INTO boroughs
    SET ?
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, post);

  // 提供数据
  return data;
}

/**
 * 更新内容
 */
export const updateTutor = async (post: tutorModel, postId: number) => {
  // 准备查询
  const statement = `
    UPDATE boroughs
    SET ?
    WHERE id = ?
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, [post, postId]);

  // 提供数据
  return data;
}

/**
 * 删除内容
 */
export const deleteTutor = async (postId: number) => {
  // 准备查询
  const statement = `
    DELETE FROM boroughs
    WHERE id = ?
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, postId);

  // 提供数据
  return data;
}

/**
 * 状态
 */
export const tutorStatusChange = async (postId: number, post: any) => {
  // 准备数据
  const statement = `
    UPDATE tutor
      SET ?
      WHERE id = ?
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, [post, postId]);
  if ((data as any).affectedRows > 0) {
    await cornerStatusChangeByType(postId, 9, 1);
  }

  // 提供数据
  return data;
};
